<?php

require_once('../../connection/_dbconnect_.php');
//require_once('m_activity_log.php');



function getShiftsUser($username){
	
	$parentArray = array();
	
	$sql = "select w.name as weekday,
				   ss.name as schedule_name,
				   ss.start_time,
				   ss.end_time,
				   qs.idqueue_shift as qshift,
				   qs.description as qshift_desc
			from schedule s \n
			join weekday w on (s.idweekday = w.idweekday) \n
			join shift_schedule ss on (s.idshift_schedule = ss.idshift_schedule) \n
			join queue_shift qs on (s.idqueue_shift = qs.idqueue_shift) \n
			join user_schedule us on (s.idschedule = us.idschedule) \n
			where us.iduser = '".$username."'\n
			order by w.idweekday ASC;";
	
	//echo $sql;exit;
	
	$result = mysql_query($sql) or die("Query: $sql \n\n".mysql_error());
	
	if (!$result) 
		return false;
	else if (mysql_num_rows($result)!=0){
		while($obj = mysql_fetch_object($result)){
			$childArray = array();		
			$childArray[] = $obj->weekday;
			$childArray[] = $obj->schedule_name;
			$childArray[] = $obj->start_time;
			$childArray[] = $obj->end_time;
			$childArray[] = $obj->qshift;
			$childArray[] = $obj->qshift_desc;
			
			$parentArray[]= $childArray;
		}
		
		//print_r($parentArray);exit;

		return $parentArray;
	} else {
		echo '<h2>No Shift data to show for user: '.$username;
		exit;
	}
}

function getShiftsUserWeek($username){
	
	$parentArray = array();
	
	$sql = "select w.name as weekday,
				   ss.name as shift_name,
				   ss.start_time,
				   ss.end_time,
				   qs.idqueue_shift as qshift,
				   qs.description as qshift_desc
			from schedule s \n
			join weekday w on (s.idweekday = w.idweekday) \n
			join shift_schedule ss on (s.idshift_schedule = ss.idshift_schedule) \n
			join queue_shift qs on (s.idqueue_shift = qs.idqueue_shift) \n
			join user_schedule us on (s.idschedule = us.idschedule) \n
			where us.iduser = '".$username."'\n
			order by w.idweekday ASC;";
	
	//echo $sql;exit;
	
	$result = mysql_query($sql) or die("Query: $sql \n\n".mysql_error());
	
	if (!$result) 
		return false;
	else if (mysql_num_rows($result)!=0){
		
		/* date processing
		 * get today's date and check for user's schedules for those days (if
		 * any) in the qshift change request table
		 */		
		$today = new DateTime('NOW');
		$today->setTimeZone(new DateTimeZone("America/Costa_Rica"));
		$timestamp = strtotime($today->format('Y-m-d'));
		$date = date('Y-m-d', strtotime('monday this week', $timestamp));
		$dateArray = explode('-', $date);		
		
		/// VOY ACA, el cambio al target es aca que empieza		
		$sql2= "select  scr.day_requested as date,
						ss.name as shift_name,
						ss.start_time,
						ss.end_time,
						qs.idqueue_shift as qshift,
						qs.description as qshift_desc
				 from shift_change_request scr
				 join shift_schedule ss on ss.idshift_schedule = scr.requestedShift
				 join schedule s on ss.idshift_schedule = s.idshift_schedule
				 join queue_shift qs on qs.idqueue_shift = s.idqueue_shift
				 join user_schedule us on us.idschedule = s.idschedule
				 where scr.day_requested between '".$dateArray[0]."-".$dateArray[1]."-".$dateArray[2]."' 
											 and '".$dateArray[0]."-".$dateArray[1] ."-".($dateArray[2] + 4)."'  
											 and request_status='Approved'
											 and scr.requesting_user = '".$username."'
											 and us.iduser = '".$username."'
				 order by scr.day_requested ASC;";
		
		$result2 = mysql_query($sql2) or die("Query: $sql2 \n\n".mysql_error());
		
		//echo mysql_num_rows($result2)."<br><br>".$sql2; exit;		
		
		if (!$result2) 
			return false;
		else if (mysql_num_rows($result2)==0) { //if there aren't any change requests for this week then move on
			
			while($obj = mysql_fetch_object($result)){
				$childArray = array();		
				$childArray[] = $obj->weekday;
				$childArray[] = $obj->shift_name;
				$childArray[] = $obj->start_time;
				$childArray[] = $obj->end_time;
				$childArray[] = $obj->qshift;
				$childArray[] = $obj->qshift_desc;
				$childArray[] = "No Change";
				
				$parentArray[]= $childArray;
			}

			return $parentArray;
			
		} elseif (mysql_num_rows($result2)!=0) { //if change requests exist then additional processing before return
			
			$swapsActive = array();	
			$weekArray = array('Monday','Tuesday','Wednesday','Thursday','Friday');
			
			// save all active changes prefixed by the weekday
			while($obj2 = mysql_fetch_object($result2)){			
				
				//add weekday depending on date
				if ($obj2->date == $dateArray[0].'-'.$dateArray[1].'-'.$dateArray[2]) { 
					$swapsActive[] = $weekArray[0];
				} elseif ($obj2->date == $dateArray[0].'-'.$dateArray[1].'-'.($dateArray[2]+1)){ 
					$swapsActive[] = $weekArray[1];
				} elseif ($obj2->date == $dateArray[0].'-'.$dateArray[1].'-'.($dateArray[2]+2)){ 
					$swapsActive[] = $weekArray[2];
				} elseif ($obj2->date == $dateArray[0].'-'.$dateArray[1].'-'.($dateArray[2]+3)){ 
					$swapsActive[] = $weekArray[3];
				} elseif ($obj2->date == $dateArray[0].'-'.$dateArray[1].'-'.($dateArray[2]+4)){ 
					$swapsActive[] = $weekArray[4];
				}

				$swapsActive[] = $obj2->shift_name;
				$swapsActive[] = $obj2->start_time;
				$swapsActive[] = $obj2->end_time;	
				$swapsActive[] = $obj2->qshift;	
				$swapsActive[] = $obj2->qshift_desc;
				$swapsActive[] = $obj2->date;

			}
			
			while($obj = mysql_fetch_object($result)){
				$childArray = array();		
				$childArray[] = $obj->weekday;
				$activeChange = "No Change";
				$changeDate = "";
				
				//check if there's a swap for this day
				$swapExists= array_search($obj->weekday, $swapsActive);
				
				if ($swapsActive[$swapExists] == $obj->weekday){
						$childArray[] = $swapsActive[$swapExists+1];
						$childArray[] = $swapsActive[$swapExists+2];	
						$childArray[] = $swapsActive[$swapExists+3];	
						$childArray[] = $swapsActive[$swapExists+4];	
						$childArray[] = $swapsActive[$swapExists+5];	
						$changeDate = $swapsActive[$swapExists+6];	
						$activeChange = "Change";
				} else {				
					$childArray[] = $obj->shift_name;
					$childArray[] = $obj->start_time;
					$childArray[] = $obj->end_time;	
					$childArray[] = $obj->qshift;	
					$childArray[] = $obj->qshift_desc;
				}
				
				$childArray[] = $activeChange;
				$childArray[] = $changeDate;
				
				$parentArray[]= $childArray;				

			}

			//print_r($parentArray);exit;
			
			return $parentArray;			
		}
	} else {
		echo '<h2>No Shift data to show for user: '.$username;
		exit;
	}
}

function loadWeekdays(){
	$parentArray = array();
	
	$sql = "select idweekday,name from weekday";
	
	$result = mysql_query($sql) or die("Query: $sql \n\n".mysql_error());
	
	if (!$result) 
		return false;
	else {
		while($obj = mysql_fetch_object($result)){
			$childArray = array();		
			$childArray[] = $obj->idweekday;
			$childArray[] = $obj->name;
			$parentArray[]= $childArray;	
			
		}
		return $parentArray;	
	}
}

function loadShifts(){
	$parentArray = array();
	
	$sql = "select idshift_schedule,name from shift_schedule";
	
	$result = mysql_query($sql) or die("Query: $sql \n\n".mysql_error());
	if (!$result) 
		return false;
	else {	
		while($obj = mysql_fetch_object($result)){
			$childArray = array();		
			$childArray[] = $obj->idshift_schedule;
			$childArray[] = $obj->name;
			$parentArray[]= $childArray;			
		}
		return $parentArray;	
	}
}

function loadUsers(){
	$parentArray = array();

	$sql = "select u.iduser
			from user u\n
			join role r on (u.idrole = r.idrole)\n
			order by r.idrole ASC, u.iduser ASC";
	
	//echo $sql;exit;
	
	$result = mysql_query($sql) or die("Query: $sql \n\n".mysql_error());
	
	while($obj = mysql_fetch_object($result)){
		$parentArray[] = $obj->iduser;
	}	
	return $parentArray;
}

function loadShiftChanges($username){
		
	$parentArray = array();
	
	$now = new DateTime('NOW');
	$now->setTimeZone(new DateTimeZone("America/Costa_Rica"));
	$today = strtotime($now->format('Y-m-d'));
	$date = date('Y-m-d', strtotime('monday this week', $today));	

	$sql= "select   scr.idshift_change as id,
					scr.day_requested as date,
					ss.name as shift_name,
					ss.start_time,
					ss.end_time,
					scr.target_user,
					scr.request_status					
			 from shift_change_request scr
			 join shift_schedule ss on ss.idshift_schedule = scr.requestedShift
			 where scr.day_requested >= '".$date."'
										 and scr.requesting_user = '".$username."'
			 order by scr.day_requested DESC;";
		
			
	//echo $sql;exit;
	
	$result = mysql_query($sql) or die("Query: $sql \n\n".mysql_error());
	
	if (!$result) 
		return false;
	else if (mysql_num_rows($result)==0) {
		echo "<h1> No data to show for user: ".$username."</h1>";
		exit;
	} else {
		while($obj = mysql_fetch_object($result)){
			$childArray = array();		
			$childArray[] = $obj->id;
			$childArray[] = $obj->date;
			$childArray[] = $obj->shift_name;
			$childArray[] = $obj->start_time;
			$childArray[] = $obj->end_time;
			$childArray[] = $obj->target_user;
			$childArray[] = $obj->request_status;
			
			$parentArray[]= $childArray;
		}
	}

	return $parentArray;


}

function cancelRequest($id,$username){
	
	$today = new DateTime('NOW');
	$today->setTimeZone(new DateTimeZone("America/Costa_Rica"));
	$date = $today->format('Y-m-d');
	
	$sql = "select t.request_status as status
			from shift_change_request t
			where t.idshift_change='".$id."';";

	$result = mysql_query($sql) or die("Query: $sql \n\n".mysql_error());
	
	if (!$result) {
		return false;
	} else {
	
		$obj = mysql_fetch_object($result);
		
		if($obj->status == "Approved") {
			$sql = "update shift_change_request 
						   set request_status='Pend. Cancelation Req. by ".$username."' 
						   where idshift_change='".$id."';";
		} else 
			$sql = "update shift_change_request 
						   set request_status='Cancelled by ".$username."' 
						   where idshift_change='".$id."';";
		
		$result = mysql_query($sql) or die("Query: $sql \n\n".mysql_error());
		
		if (!$result) {
			return false;
		} else {
			
			//$result=addLogEntry($date->format('Y-m-d'), "Schedule Change Request=".$id." was denied");	
			return true;
		}
	}
}

function isValidDate($date) {

	$dateArray = explode('-', $date);
	if (checkdate($dateArray[1],$dateArray[2],$dateArray[0]))
		return true;
	else
		return false;
}

function submitRequestByShift($username,$date,$data){
	
	// check if this user has a shift change request for that day
	$sql = "select request_status as status
				   from shift_change_request
				   where day_requested = '".$date."'
				   and requesting_user = '".$username."'
				   and (request_status = 'Pending' or request_status = 'Approved');";

	$result = mysql_query($sql) or die("Query: $sql \n\n".mysql_error());
	
	if (!$result) 
		return false;
	elseif (mysql_num_rows($result)!=0) {
		$obj = mysql_fetch_object($result);
		echo "<h3>You already have a change request for <i>".$date."</i>  with current status= <i>'".$obj->status."'</i><br>
			  Please try with a different date or go to the <i>'Manage Shift Changes'</i> area to cancel the current change.</h3>";
		echo '<br><input type="button" value="OK" onclick="location.reload();"/>';	
		exit;
	}
	
	//check if the user has a schedule for that day at all
	$hasScheduleForThatDay= getUserPermanentShiftForDate($username,$date);
	
	
	
	//prepare query to insert request as Pending
	$sql = "insert into shift_change_request
						values (NULL,
								'".$username."', \n
								NULL, \n
								".$data.", \n
								'".$date."', \n
								'Pending', \n
								NULL);";	
	
	//check if calendar day exists before inserting the request
	$sql2 = "select * from calendar_day where idcalendar_day = '".$date."'";
	//echo $sql2;exit;
	$result = mysql_query($sql2) or die("Query: $sql2 \n\n".mysql_error());
	
	if (!$result) 
		return false;
	elseif (mysql_num_rows($result)!=0) {	

			//calendar day exists, insert row
			$result = mysql_query($sql) or die("Query: $sql \n\n".mysql_error());
			
			if (!$result) {
				return false;
				exit;
			} else {
				return true;
				exit;
			}
	} elseif (mysql_num_rows($result)==0) {
		
		$sql2 = "insert into calendar_day values ('".$date."')";

		$result = mysql_query($sql2) or die("Query: $sql2 \n\n".mysql_error());
		
		if (!$result) 
			return false; 
		else {
		
			//calendar day created, insert row
			$result = mysql_query($sql) or die("Query: $sql \n\n".mysql_error());
			
			if (!$result) 
				return false;
			else {
				return true;
			}			
		}
	}
}

function submitRequestByUser($username,$date,$data){
	
	// check if this user or target user has a shift change request for that day
	$sql = "select request_status as status
				   from shift_change_request
				   where day_requested = '".$date."'
				   and (
						requesting_user = '".$username."' 
						or target_user = '".$username."'
						or requesting_user = '".$data."' 
						or target_user = '".$data."'
						);";

	//echo "testing at submitRequestByUser 1 <br>".$sql;exit;
				   
	$result = mysql_query($sql) or die("Query: $sql \n\n".mysql_error());
	
	if (!$result) 
		return false;
	elseif (mysql_num_rows($result)!=0) {
		$obj = mysql_fetch_object($result);
		echo "<h3>You, or the person that you are want to change with, already have/has a change request for <i>".$date."</i>  with current status= <i>'".$obj->status."'</i><br>
			  Please try with a different date, or user, or go to the <i>'Manage Shift Changes'</i> area to cancel the current change.</h3>";
		echo '<br><input type="button" value="OK" onclick="location.reload();"/>';	
		exit;
	}
	
	//get target user's shift for that day
	$currentUserShift =  getUserPermanentShiftForDate($username,$date);
	$targetUserShift = getUserPermanentShiftForDate($data,$date);
	
	//prepare query to insert request as Pending
	$sql = "insert into shift_change_request
						values (NULL,
								'".$username."', \n
								".$currentUserShift->shift_id.", \n
								".$targetUserShift->shift_id.", \n" // <- target shift
								."'".$date."', \n
								'Pending', \n
								'".$data."');";	// <-target user
	
	//check if calendar day exists before inserting the request
	$sql2 = "select * from calendar_day where idcalendar_day = '".$date."'";
	
	
	//echo "testing at submitRequestByUser 2 <br>".$sql;exit;
	
	$result = mysql_query($sql2) or die("Query: $sql2 \n\n".mysql_error());
	
	if (!$result) 
		return false;
	elseif (mysql_num_rows($result)!=0) {	

			//calendar day exists, insert row
			$result = mysql_query($sql) or die("Query: $sql \n\n".mysql_error());
			
			if (!$result) {
				return false;
				exit;
			} else {
				return true;
				exit;
			}
	} elseif (mysql_num_rows($result)==0) {
		
		$sql2 = "insert into calendar_day values ('".$date."')";

		$result = mysql_query($sql2) or die("Query: $sql2 \n\n".mysql_error());
		
		if (!$result) 
			return false; 
		else {
		
			//calendar day created, now insert row
			$result = mysql_query($sql) or die("Query: $sql \n\n".mysql_error());
			
			if (!$result) 
				return false;
			else {
				return true;
			}			
		}
	}
}

// WARNING: this function won't check for shift changes
function getUserPermanentShiftForDate($user,$date) {

	$dayToEval = new DateTime('NOW');
	$dayToEval->setTimeZone(new DateTimeZone("America/Costa_Rica"));
	$dateArray = explode('-', $date);
	$dayToEval->setDate($dateArray[0], $dateArray[1], $dateArray[2] );
	$weekday = $dayToEval->format('l');

	$parentArray = array();
	
	$sql = "select s.idschedule as schedule,
				   ss.idshift_schedule as shift_id,
				   ss.name as shift_name,
				   ss.start_time as shift_start,
				   ss.end_time as shift_end,
				   s.idqueue_shift as qshift
			from schedule s \n
			join weekday w on (s.idweekday = w.idweekday) \n
			join shift_schedule ss on (s.idshift_schedule = ss.idshift_schedule) \n
			join queue_shift qs on (s.idqueue_shift = qs.idqueue_shift) \n
			join user_schedule us on (s.idschedule = us.idschedule) \n
			where us.iduser = '".$user."' and w.name = '".$weekday."';";
	
	//echo "testing at getUserPermanentShiftForDate <br> date = ".$dayToEval->format("Y-m-d")."<br>".$sql;exit;
	
	$result = mysql_query($sql) or die("Query: $sql \n\n".mysql_error());
	
	if (!$result) 
		return false;
	elseif (mysql_num_rows($result)==0) {
		echo "<h3><font color='red'> ERROR: </font>You, or the user you want to change with, don't have a schedule defined for <i>".$date."</i>... or namely for <i>".$weekday."s.</i><br>
			  Please try a different date, or user, or have a manager define a schedule for you or that user for that day.</h3>";
		echo '<br><input type="button" value="OK" onclick="location.reload();"/>';	
		exit;
	} elseif (mysql_num_rows($result)!=0) {
		
		$obj = mysql_fetch_object($result);
		
		return $obj;
	} else {
		echo "uncatched error at getUserPermanentShiftForDate in m_shifts";
		exit;
	}
}

?>